package com.css.project.oa.cash.controller;

import static org.springframework.util.StreamUtils.BUFFER_SIZE;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.css.common.utils.ConvertNumberToUpperUtils;
import com.css.common.utils.DateUtils;
import com.css.common.utils.file.FileUtils;
import com.css.framework.aspectj.lang.annotation.Log;
import com.css.framework.aspectj.lang.enums.BusinessType;
import com.css.framework.config.RuoYiConfig;
import com.css.framework.interceptor.annotation.RepeatSubmit;
import com.css.framework.web.controller.BaseController;
import com.css.framework.web.domain.AjaxResult;
import com.css.framework.web.page.TableDataInfo;
import com.css.project.oa.cash.domain.CfContent;
import com.css.project.oa.cash.domain.ProCash;
import com.css.project.oa.cash.domain.ProCashItem;
import com.css.project.oa.cash.service.IProCashService;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import springfox.documentation.annotations.ApiIgnore;

@Api(value = "/cash ",description ="报销单管理")
@RestController
@RequestMapping(value = "/cash")
public class CashExpenseController extends BaseController {

    Logger log = LoggerFactory.getLogger(CashExpenseController.class);
//    private static final Logger log = LoggerFactory.getLogger(HttpUtils.class);

    @Autowired
    public IProCashService proCashServicel;

    /**
        * @Author 陈泽恩
        * @Description  获取列表
        * @Date 15:25 20-05-09,0009
        * @Param [proCash]
        * @return com.css.framework.web.page.TableDataInfo
    **/
    @ApiOperation("获取报销单列表")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "expenseName", value = "报销人姓名", paramType="query", required = false),
            @ApiImplicitParam(name = "cashTimeStart", value = "费用报销日期   ", paramType="query", required = false)
    })
    @RequestMapping(value = "/list", method = RequestMethod.GET)
    public TableDataInfo list(@ApiIgnore ProCash proCash){
        startPage();
        List<ProCash> list = proCashServicel.getCashList(proCash);
        return getDataTable(list);
    }

    /**
        * @Author 陈泽恩
        * @Description  新增或者保存报销单
        * @Date 16:27 20-05-12,0012
        * @Param [proCash, map]
        * @return com.css.framework.web.domain.AjaxResult
    **/
    @Log(title = "报销单管理", businessType = BusinessType.INSERT)
    @ApiOperation("报销单新增或修改")
    @PostMapping(value = "/addUpdateCash")
    @RepeatSubmit
    public AjaxResult addUpdateCash(@RequestBody ProCash proCash, @RequestBody Map<String, Object> map) {
        proCashServicel.addUpdateCash(proCash, map.get("itemDataStr").toString());
        return AjaxResult.success();
    }

    @Log(title = "报销单管理", businessType = BusinessType.INSERT)
    @PostMapping(value = "/addUpdateCashYD")
    @RepeatSubmit
    public AjaxResult addUpdateCashYD(@RequestBody ProCash proCash) {
        proCashServicel.addUpdateCashYD(proCash);
        return AjaxResult.success();
    }
    
    @Log(title = "报销单管理", businessType = BusinessType.UPDATE)
    @ApiOperation("报销单餐费明细修改")
    @PostMapping(value = "/updateProCashItemForDinner")
    @RepeatSubmit
    public AjaxResult updateProCashItemForDinner(@RequestBody ProCashItem item) {
        proCashServicel.updateProCashItemForDinner(item);
        return AjaxResult.success();
    }
    /**
     * 现金报销单上报
     */
     @Log(title = "现金报销单上报", businessType = BusinessType.UPDATE)
    @PutMapping("/reportCash/{ids}")
    public AjaxResult report(@PathVariable Long[] ids)
    {
        return toAjax(proCashServicel.reportCashByIds(ids));
    }

    /**
     * 现金报销单调整修改上报
     */
    @Log(title = "现金报销单调整修改上报", businessType = BusinessType.UPDATE)
    @PutMapping("/reportCashAgain/{ids}")
    public AjaxResult reportAgain(@PathVariable Long[] ids)
    {
        return toAjax(proCashServicel.reportCashAgainByIds(ids));
    }

    /**
        * @Author 陈泽恩
        * @Description  根据报销单id获取详情
        * @Date 16:33 20-05-12,0012
        * @Param [cashId]
        * @return com.css.framework.web.domain.AjaxResult
    **/
    @GetMapping(value= "/getCashById")
    @ApiOperation("根据主键获取报销单详情")
    public AjaxResult getCashById(Long cashId){
        Map<String, Object> map = proCashServicel.getCashById(cashId);
        return AjaxResult.success(map);
    }

    /**
        * @Author 陈泽恩
        * @Description  报销单删除
        * @Date 14:08 20-05-18,0018
        * @Param [cashIds]
        * @return com.css.framework.web.domain.AjaxResult
    **/
    @PreAuthorize("@ss.hasPermi('pro:cash:delete')")
    @Log(title = "报销单管理", businessType = BusinessType.DELETE)
    @RequestMapping(value = "/deleteCash/{cashIds}")
    public AjaxResult deleteCash(@PathVariable Long[] cashIds){
        return toAjax(proCashServicel.deleteCash(cashIds));
    }

    /**
     * 获取打印报销单主信息
     * @param id
     * @return
     */
    @GetMapping(value = "getPrintMainInfo/{id}")
    public AjaxResult getPrintMainInfo(@PathVariable("id") Long id)
    {
        return AjaxResult.success(proCashServicel.selectProCashPrintInfoById(id));
    }

    @RequestMapping(value = "/itemList/{cashId}", method = RequestMethod.GET)
    public TableDataInfo getProCashItemList(@PathVariable("cashId") Long cashId){
        List<ProCashItem> list = proCashServicel.getProCashItemList(cashId);
        return getDataTable(list);
    }

    @RequestMapping(value = "/itemMainList/{cashId}", method = RequestMethod.GET)
    public TableDataInfo getProCashItemMainList(@PathVariable("cashId") Long cashId){
        List<ProCashItem> list = proCashServicel.getProCashItemMainList(cashId);
        return getDataTable(list);
    }

    /**
     * 获取上传的pdf
     * @param id
     * @return
     */
    @GetMapping(value = "getPDFInfo/{id}")
    public AjaxResult getPDFInfo(@PathVariable("id") Long id)
    {
        return AjaxResult.success(proCashServicel.getFileInfoByCashAndFileType(id,".pdf"));
    }

    /**
     * 导出明细表
     * @param cashId
     * @param request
     * @param response
     * @return com.css.framework.web.domain.AjaxResult
     * @date 20-06-04,0004 10:11
     */
    @GetMapping(value="/mxExport/{cashId}")
    public AjaxResult mxExport(@PathVariable("cashId") Long cashId,HttpServletRequest request, HttpServletResponse response)
    {
        String fileName = "";
    	try {
            fileName = createBxmxFile(cashId,response);
            return AjaxResult.success(fileName);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.success("");
        }
    }

    /**
     * 导出报销单
     * @param cashId
     * @param request
     * @param response
     * @return com.css.framework.web.domain.AjaxResult
     * @author 陈泽恩
     * @date 20-06-04,0004 10:12
     */
    @GetMapping(value="/bxExport/{cashId}")
    public AjaxResult bxExport(@PathVariable("cashId") Long cashId,HttpServletRequest request, HttpServletResponse response)
    {
        String fileName = "";
        try {
            fileName = createBxdFile(cashId,response);
            return AjaxResult.success(fileName);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.success("");
        }
    }

    /**
     *
    * @Title: cdqdExport
    * @Description: 传递清单导出
    * @param @param cashId
    * @param @param request
    * @param @param response
    * @param @return    设定文件
    * @author ShiChao
    * @return AjaxResult    返回类型
    * @throws
     */
    @GetMapping(value="/cdqdExport/{cashId}")
    public AjaxResult cdqdExport(@PathVariable("cashId") Long cashId,HttpServletRequest request, HttpServletResponse response)
    {
        String fileName = "";
        try {
            fileName = createCdqdFile(cashId,response);
            return AjaxResult.success(fileName);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.success("");
        }
    }
    /**
     * 
    * @Title: personExport 
    * @Description: 单人报销单导出
    * @param @param cashId
    * @param @param request
    * @param @param response
    * @param @return    设定文件 
    * @author ShiChao 
    * @return AjaxResult    返回类型 
    * @throws
     */
    @GetMapping(value="/personExport/{cashId}")
    public AjaxResult personExport(@PathVariable Long cashId,HttpServletRequest request, HttpServletResponse response)
    {
        OutputStream out = null;
        ZipOutputStream zos = null ;
        String zipStr = "我的报销单.zip";
        List<File> listFiles = new ArrayList<File>();
        List<String> listFoldNames = new ArrayList<String>();
        try {
            //生成相应文件
        	String fileName = createBxdFile(cashId,response);//报销单
        	 if(!"".equals(fileName)){
                 listFiles.add(new File(getAbsoluteFile(fileName)));
                 listFoldNames.add(fileName.split("/")[0]);
             }
        	fileName = createBxmxFile(cashId,response);//报销明细
            if(!"".equals(fileName)){
                listFiles.add(new File(getAbsoluteFile(fileName)));
                listFoldNames.add(fileName.split("/")[0]);
            }
            fileName = createCdqdFile(cashId,response);//传递清单
            if(!"".equals(fileName)){
                listFiles.add(new File(getAbsoluteFile(fileName)));
                listFoldNames.add(fileName.split("/")[0]);
            }
            fileName = createCfbxFile(cashId,response);//餐费报销
            if(!"".equals(fileName)){
                listFiles.add(new File(getAbsoluteFile(fileName)));
                listFoldNames.add(fileName.split("/")[0]);
            }
            fileName = createJbcfFile(cashId,response);//加班餐费
            if(!"".equals(fileName)){
                listFiles.add(new File(getAbsoluteFile(fileName)));
                listFoldNames.add(fileName.split("/")[0]);
            }
            //生成压缩包
            out = new FileOutputStream(getAbsoluteFile(zipStr));
            zos = new ZipOutputStream(out);
            for (int j=0;j<listFiles.size();j++) {
                File file = listFiles.get(j);
                String foldName = listFoldNames.get(j);
                byte[] buf = new byte[BUFFER_SIZE];
                zos.putNextEntry(new ZipEntry(foldName+"/"+file.getName()));
                // copy文件到zip输出流中
                int len;
                FileInputStream in = new FileInputStream(file);
                while ((len = in.read(buf)) != -1){
                    zos.write(buf, 0, len);
                }
                // Complete the entry
                zos.closeEntry();
                in.close();
            }
            //删除非压缩文件
            for(int k=0;k<listFoldNames.size();k++){
                FileUtils.deleteDir(getAbsoluteFile(listFoldNames.get(k)));
            }
            zos.close();
            return AjaxResult.success(zipStr);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.success("");
        } finally {
            try {
                out.flush();
                out.close();
                zos.flush();
                zos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 批量导出，以zip的形式
     * @param cashIds
     * @param request
     * @param response
     * @return
     */
    @GetMapping(value="/allExport/{cashIds}")
    public AjaxResult allExport(@PathVariable Long[] cashIds,HttpServletRequest request, HttpServletResponse response)
    {
        OutputStream out = null;
        ZipOutputStream zos = null ;
        String zipStr = "my.zip";
        List<File> listFiles = new ArrayList<File>();
        List<String> listFoldNames = new ArrayList<String>();
        try {
            //生成相应文件
            for(int i=0;i<cashIds.length;i++){
                Long cashId = cashIds[i];
                String fileName = createBxmxFile(cashId,response);
                if(!"".equals(fileName)){
                    listFiles.add(new File(getAbsoluteFile(fileName)));
                    listFoldNames.add(fileName.split("/")[0]);
                }
                fileName = createCdqdFile(cashId,response);
                if(!"".equals(fileName)){
                    listFiles.add(new File(getAbsoluteFile(fileName)));
                    listFoldNames.add(fileName.split("/")[0]);
                }
            }
            //生成压缩包
            out = new FileOutputStream(getAbsoluteFile(zipStr));
            zos = new ZipOutputStream(out);
            for (int j=0;j<listFiles.size();j++) {
                File file = listFiles.get(j);
                String foldName = listFoldNames.get(j);
                byte[] buf = new byte[BUFFER_SIZE];
                zos.putNextEntry(new ZipEntry(foldName+"/"+file.getName()));
                // copy文件到zip输出流中
                int len;
                FileInputStream in = new FileInputStream(file);
                while ((len = in.read(buf)) != -1){
                    zos.write(buf, 0, len);
                }
                // Complete the entry
                zos.closeEntry();
                in.close();
            }
            //删除非压缩文件
            for(int k=0;k<listFoldNames.size();k++){
                FileUtils.deleteDir(getAbsoluteFile(listFoldNames.get(k)));
            }
            zos.close();
            return AjaxResult.success(zipStr);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.success("");
        } finally {
            try {
                out.flush();
                out.close();
                zos.flush();
                zos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 生成传递清单
     * @param cashId
     * @param response
     * @return 文件名称
     */
    private String createCdqdFile( Long cashId,HttpServletResponse response) throws Exception{
        String fileName = "";
//        String templateFile = this.getClass().getResource("/").getPath().replaceAll("^\\/", "") + "/templete/cdqd.xlsx";
        String templateFile = "templete/cdqd.xlsx";
        Map<String, Object> map = new HashMap<String, Object>();
        ProCash mainInfo = proCashServicel.selectProCashPrintInfoById(cashId);
        //解决文件名是中文乱码问题
        //map.put("fileName", "报销明细表") ;
        map.put("mainInfo", mainInfo);
        String dayStr = "";
        if(mainInfo.getCostTimeStart().equals(mainInfo.getCostTimeEnd())) {
            dayStr = mainInfo.getCostTimeStart();
        }else {
            dayStr = mainInfo.getCostTimeStart()+"至"+mainInfo.getCostTimeEnd();
        }
        map.put("dayStr", dayStr);
        String expenseMonthStr = getLaseMonthStr(mainInfo.getExpenseTime(),"yyyy年MM月");
        fileName = mainInfo.getExpenseNamePrint()+"/数字城市事业本部传递清单——（"+mainInfo.getExpenseNamePrint()+expenseMonthStr+"）.xlsx";
        FileUtils.createFileByTemplate(fileName,map,templateFile,response);
       return fileName;
    }
    /**
     * 生成报销明细表
     * @param cashId
     * @param response
     * @return 文件名称
     */
    private String createBxmxFile( Long cashId,HttpServletResponse response) throws Exception{
        String fileName = "";
//        String templateFile = this.getClass().getResource("/").getPath().replaceAll("^\\/", "") + "/templete/xjbxmx.xls";
        String templateFile = "templete/xjbxmx.xls";
        Map<String, Object> map = new HashMap<String, Object>() ;
        ProCash mainInfo = proCashServicel.selectProCashPrintInfoById(cashId);
        if(mainInfo.getProductNamePrint() == null || "".equals(mainInfo.getProductNamePrint() )) {
            mainInfo.setProductNamePrint("  ");
        }
        if(mainInfo.getProductFinanceNoPrint() == null || "".equals(mainInfo.getProductFinanceNoPrint() )) {
            mainInfo.setProductFinanceNoPrint(" ");
        }
        List<ProCashItem> list = proCashServicel.getProCashItemList(cashId);
        for(ProCashItem proCashItem : list) {
            if("1".equals(proCashItem.getExpenseContent())) {
                proCashItem.setCostTimeStr(proCashItem.getCostTimeStr().substring(0,8));
            }
        }
        if(list != null && list.size()<5){
            int i = 5- list.size();
            for(;i>0;i--){
                list.add(new ProCashItem());
            }
        }
        //解决文件名是中文乱码问题
        //map.put("fileName", "报销明细表") ;
        map.put("mainInfo", mainInfo);
        map.put("xjbxmxList", list) ;
        String expenseMonthStr = getLaseMonthStr(mainInfo.getExpenseTime(),"yyyyMM");
        fileName = mainInfo.getExpenseNamePrint()+"/现金报销明细表_"+mainInfo.getExpenseNamePrint()+expenseMonthStr+".xls";
        FileUtils.createFileByTemplate(fileName,map,templateFile,response);
        return fileName;
    }

    /**
     * 导出报销单
     * @param cashId
     * @param response
     * @return java.lang.String
     * @author 陈泽恩
     * @date 20-06-04,0004 10:13
     */
    private String createBxdFile( Long cashId,HttpServletResponse response) throws Exception{
        String fileName = "";
//        String templateFile = this.getClass().getResource("/").getPath().replaceAll("^\\/", "") + "/templete/xjbxd.xls";
        String templateFile = "templete/xjbxd.xls";
        Map<String, Object> map = new HashMap<String, Object>();
        ProCash cash = proCashServicel.selectProCashPrintInfoById(cashId);
        if(cash != null){
            cash.setExpenseMoneyDxPrint(ConvertNumberToUpperUtils.digitCapital(cash.getTotalAmount()));
        }
        List<ProCashItem> list = proCashServicel.getProCashItemMainList(cashId);
        if(list != null && list.size()<5){
            int i = 5- list.size();
            for(;i>0;i--){
                list.add(new ProCashItem());
            }
        }
        map.put("mainInfo", cash);
        map.put("xjbxmxList", list);
        //因为开始时间和结束时间都是零点,两者只差少一天,这里手动加1
        map.put("cashTimeLength", DateUtils.getDatePoorDay(cash.getCashTimeEnd(),cash.getCashTimeStart())+1);
        String expenseMonthStr = getLaseMonthStr(cash.getExpenseTime(),"yyyyMM");
        fileName = cash.getExpenseNamePrint()+"/现金报销单_"+cash.getExpenseNamePrint()+expenseMonthStr+".xls";
        FileUtils.createFileByTemplate(fileName,map,templateFile,response);
        return fileName;
    }
    /**
     * 
    * @Title: createCfbxFile 
    * @Description: 生成餐费报销
    * @param @param cashId
    * @param @param response
    * @param @return
    * @param @throws Exception    设定文件 
    * @author ShiChao 
    * @return String    返回类型 
    * @throws
     */
    private String createCfbxFile( Long cashId,HttpServletResponse response) throws Exception{
        String fileName = "";
//      String templateFile = this.getClass().getResource("/").getPath().replaceAll("^\\/", "") + "/templete/xjbxd.xls";
        String templateFile = "templete/cfbx.xls";
        Map<String, Object> map = new HashMap<String, Object>();
        ProCash mainInfo = proCashServicel.selectProCashPrintInfoById(cashId);
        List<ProCashItem> list = proCashServicel.getProCashItemList(cashId);
        List<CfContent> cfList = new ArrayList<CfContent>();
        SimpleDateFormat yFormat = new SimpleDateFormat("yyyy");
    	SimpleDateFormat mFormat = new SimpleDateFormat("MM");
    	SimpleDateFormat dFormat = new SimpleDateFormat("dd");
        for(int i=0;i<list.size();i++) {
        	ProCashItem cashItem = list.get(i);
        	if("3".equals(cashItem.getExpenseContent())) {
        		String year = yFormat.format(cashItem.getCostTime());
        		String month = 	mFormat.format(cashItem.getCostTime());
        		String day = dFormat.format(cashItem.getCostTime());
        		CfContent cf = new CfContent();
        		cf.setCostTime(year+"."+month+"."+day);
        		cf.setExpenseMoney(cashItem.getExpenseMoney());
        		cf.setCostCity(cashItem.getCostCity());
        		cf.setHotel(cashItem.getHotel());
        		cf.setBePersonName(cashItem.getBePersonName());
        		cf.setPosition(cashItem.getPosition());
        		cf.setExpenseRemark(cashItem.getExpenseRemark());
        		cf.setReasonForDinner(cashItem.getReasonForDinnerName());
        		cfList.add(cf);
        	}
        }
        if(cfList.size()==0) {
        	return "";
        }
        map.put("mainInfo", mainInfo);
        map.put("cfbxList", cfList);
        String expenseMonthStr = getLaseMonthStr(mainInfo.getExpenseTime(),"yyyyMM");
        fileName = mainInfo.getExpenseNamePrint()+"/餐费报销_"+mainInfo.getExpenseNamePrint()+expenseMonthStr+".xls";
        FileUtils.createFileByTemplate(fileName,map,templateFile,response);
        return fileName;
    }
    /**
     * 
    * @Title: createJbcfFile 
    * @Description: 加班餐费 
    * @param @param cashId
    * @param @param response
    * @param @return
    * @param @throws Exception    设定文件 
    * @author ShiChao 
    * @return String    返回类型 
    * @throws
     */
    private String createJbcfFile( Long cashId,HttpServletResponse response) throws Exception{
        String fileName = "";
        ProCash mainInfo = proCashServicel.selectProCashPrintInfoById(cashId);
        List<ProCashItem> list = proCashServicel.getProCashItemList(cashId);
        List<CfContent> jbList = new ArrayList<CfContent>();
    	SimpleDateFormat mFormat = new SimpleDateFormat("MM");
    	SimpleDateFormat dFormat = new SimpleDateFormat("dd");
    	Double xehj = 0.00;//限额合计
		Double sjhj = 0.00;//实际合计
		String cfMon = "";//餐时 月
		Map<String,Integer> jbPersonMap = new HashMap<String,Integer>();
    	for(int i=0;i<list.size();i++) {
        	ProCashItem cashItem = list.get(i);
        	if("3".equals(cashItem.getExpenseContent())) {
        		String month = 	mFormat.format(cashItem.getCostTime());
        		cfMon = month;
        		String day = dFormat.format(cashItem.getCostTime());
        		if("1".equals(cashItem.getReasonForDinner())) {
        			Double thisXehj = 0.00;//限额合计
        			String[] bePersonNames = cashItem.getBePersonName().split(",");
        			//获取加班人员和人员总消费
        			for(int j=0;j<bePersonNames.length;j++) {
        				if(jbPersonMap.get(bePersonNames[j]) != null) {
        					jbPersonMap.put(bePersonNames[j], jbPersonMap.get(bePersonNames[j])+15);
        				}else {
        					jbPersonMap.put(bePersonNames[j], 15);
        				}
        				thisXehj += 15;
        			}
        			CfContent jb = new CfContent();
            		jb.setCostTime2(month+"."+day);
            		jb.setExpenseMoney(cashItem.getExpenseMoney());
            		jb.setMealTime(cashItem.getMealTimeName());
            		jb.setBePersonName(cashItem.getBePersonName());
            		jb.setXehj(thisXehj);
            		jbList.add(jb);
            		sjhj += cashItem.getExpenseMoney();
        		}
        		
        	}
        }
    	List<CfContent> jbPersonList = new ArrayList<CfContent>();
    	int cellCount = 5;
    	for (String personName : jbPersonMap.keySet()) {
    		CfContent jbPerson = new CfContent();
    		jbPerson.setJbPersonName(personName);
    		jbPerson.setJbPersonTotalCost(Double.valueOf(jbPersonMap.get(personName)));
    		jbPersonList.add(jbPerson);
    		xehj += Double.valueOf(jbPersonMap.get(personName));
    		cellCount++;
        }
        if(jbList.size()==0) {
        	return "";
        }else {
        	String expenseMonthStr = getLaseMonthStr(mainInfo.getExpenseTime(),"yyyyMM");
        	fileName = mainInfo.getExpenseNamePrint()+"/加班餐费_"+mainInfo.getExpenseNamePrint()+expenseMonthStr+".xls";
        	HSSFWorkbook wb = createJbcfExcel(cfMon,cellCount,jbList,jbPersonList,xehj,sjhj);
        	OutputStream out = null;  
        	response.reset();
	        response.setContentType("application/octet-stream; charset=utf-8");
	        response.setHeader("Content-Disposition", "attachment; filename=");
	        String downloadPath = RuoYiConfig.getDownloadPath() + fileName;
	        File desc = new File(downloadPath);
	        if (!desc.getParentFile().exists())
	        {
	            desc.getParentFile().mkdirs();
	        }
	        out = new FileOutputStream(downloadPath);
	        wb.write(out);
	        out.flush();
	        out.close();
        }
        return fileName;
    }
    private HSSFWorkbook createJbcfExcel(String cfMon,int cellCount,List<CfContent> jbList,List<CfContent> jbPersonList,Double xehj,Double sjhj) {
    	HSSFWorkbook workbook=new HSSFWorkbook();
    	//创建工作表对象
    	int rowNum = 5;
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow(rowNum);
        HSSFCell cell = row.createCell(0);
        HSSFCellStyle style = workbook.createCellStyle();
        cell.setCellValue("石家庄项目组加班餐费记录表（石家庄）");
        cell.setCellStyle(style);
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        style.setBorderBottom(BorderStyle.THIN);// 下边框
        style.setBorderLeft(BorderStyle.THIN);// 左边框
        style.setBorderRight(BorderStyle.THIN);// 右边框
        style.setBorderTop(BorderStyle.THIN);// 上边框
        CellRangeAddress callRangeAddress = new CellRangeAddress(5,5,0,cellCount-1);//起始行,结束行,起始列,结束列
        sheet.addMergedRegion(callRangeAddress);
        //创建表头
        rowNum++;
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue(cfMon+"月/日");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("餐时");
        cell.setCellStyle(style);
        for(int k=0;k<jbPersonList.size();k++) {
        	cell = row.createCell(2+k);
            cell.setCellValue(jbPersonList.get(k).getJbPersonName());
            cell.setCellStyle(style);
        }
        cell = row.createCell(cellCount-3);
        cell.setCellValue("限额合计（元）");
        cell.setCellStyle(style);
        cell = row.createCell(cellCount-2);
        cell.setCellValue("实际花费（元）");
        cell.setCellStyle(style);
        cell = row.createCell(cellCount-1);
        cell.setCellValue("备注");
        cell.setCellStyle(style);
        //创建内容
        for(int i=0;i<jbList.size();i++) {
        	rowNum++;
        	row = sheet.createRow(rowNum);
        	CfContent jb = jbList.get(i);
        	cell = row.createCell(0);
            cell.setCellValue(jb.getCostTime2());
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(jb.getMealTime());
            cell.setCellStyle(style);
            for(int k=0;k<jbPersonList.size();k++) {
            	cell = row.createCell(2+k);
            	if(jb.getBePersonName().indexOf(jbPersonList.get(k).getJbPersonName())>-1) {
            		cell.setCellValue(15);
            	}else {
            		cell.setCellValue("");
            	}
                cell.setCellStyle(style);
            }
            cell = row.createCell(cellCount-3);
            cell.setCellValue(jb.getXehj());
            cell.setCellStyle(style);
            cell = row.createCell(cellCount-2);
            cell.setCellValue(jb.getExpenseMoney());
            cell.setCellStyle(style);
            cell = row.createCell(cellCount-1);
            cell.setCellValue("");
            cell.setCellStyle(style);
        }
        //创建每人合计行
        rowNum++;
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellStyle(style);
        for(int k=0;k<jbPersonList.size();k++) {
        	cell = row.createCell(2+k);
        	cell.setCellStyle(style);
        }
        cell = row.createCell(cellCount-3);
        cell.setCellValue("限额合计：");
        cell.setCellStyle(style);
        cell = row.createCell(cellCount-2);
        cell.setCellValue("实际发生合计：");
        cell.setCellStyle(style);
        cell = row.createCell(cellCount-1);
        cell.setCellValue("");
        cell.setCellStyle(style);
        //创建最终合计行
        rowNum++;
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("合计");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellStyle(style);
        for(int k=0;k<jbPersonList.size();k++) {
        	cell = row.createCell(2+k);
            cell.setCellValue(jbPersonList.get(k).getJbPersonTotalCost());
            cell.setCellStyle(style);
        }
        cell = row.createCell(cellCount-3);
        cell.setCellValue(xehj);
        cell.setCellStyle(style);
        cell = row.createCell(cellCount-2);
        cell.setCellValue(sjhj);
        cell.setCellStyle(style);
        cell = row.createCell(cellCount-1);
        cell.setCellStyle(style);
        return workbook;
    }
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /**
        * @Author 陈泽恩
        * @Description  根据cashid查询附件信息
        * @Date 17:09 20-05-20,0020
        * @Param [cashId]
        * @return com.css.framework.web.domain.AjaxResult
    **/
    @GetMapping("/getFileInfoByCash")
    public AjaxResult getFileInfoByCash (Long cashId) {
        return AjaxResult.success(proCashServicel.getFileInfoByCash(cashId));
    }
    /**
     * 
    * @Title: getLaseMonthStr 
    * @Description: 获取上个月日期，用于文件名
    * @param @param ExpenseTime
    * @param @param formatStr
    * @param @return    设定文件 
    * @author ShiChao 
    * @return String    返回类型 
    * @throws
     */
    private String getLaseMonthStr(Date ExpenseTime,String formatStr) {
    	SimpleDateFormat format = new SimpleDateFormat(formatStr);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(ExpenseTime);
        calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1); // 设置为上一个月
        ExpenseTime = calendar.getTime();
        return format.format(ExpenseTime);
    }
}
